# models.py
from extensions import db
from flask_login import UserMixin
from werkzeug.security import generate_password_hash, check_password_hash

from datetime import date
from decimal import Decimal

# ✅ 中间表定义（必须放在模型类之前）
user_permissions = db.Table(
    "user_permissions",
    db.Column("user_id", db.Integer, db.ForeignKey("users.id"), primary_key=True),
    db.Column(
        "permission_id", db.Integer, db.ForeignKey("permissions.id"), primary_key=True
    ),
)

user_roles = db.Table(
    "user_roles",
    db.Column("user_id", db.Integer, db.ForeignKey("users.id"), primary_key=True),
    db.Column("role_id", db.Integer, db.ForeignKey("roles.id"), primary_key=True),
)

role_permissions = db.Table(
    "role_permissions",
    db.Column("role_id", db.Integer, db.ForeignKey("roles.id"), primary_key=True),
    db.Column(
        "permission_id", db.Integer, db.ForeignKey("permissions.id"), primary_key=True
    ),
)


class Permission(db.Model):
    __tablename__ = "permissions"

    id = db.Column(db.Integer, primary_key=True)
    codename = db.Column(db.String(100), unique=True, nullable=False)
    name = db.Column(db.String(255), nullable=False)
    content_type = db.Column(db.String(100), nullable=False)  # 如 'product', 'user'

    # 反向关联：拥有此权限的组和用户
    roles = db.relationship(
        "Role", secondary="role_permissions", back_populates="permissions"
    )
    users = db.relationship(
        "User", secondary="user_permissions", back_populates="permissions"
    )


class Role(db.Model):
    __tablename__ = "roles"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)

    permissions = db.relationship(
        "Permission", secondary="role_permissions", back_populates="roles"
    )
    users = db.relationship("User", secondary="user_roles", back_populates="roles")


class User(db.Model, UserMixin):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(
        db.String(100), unique=True, nullable=False
    )  # 登录时用到的用户名
    password_hash = db.Column(db.String(128), nullable=False)  # 密码hash值
    name_cn = db.Column(db.String(100))  # 用户的真实姓名
    name_en = db.Column(db.String(100))  # 用户的英文名
    phone = db.Column(db.String(20))
    email = db.Column(db.String(100))
    wechat = db.Column(db.String(255))
    whatsapp = db.Column(db.String(255))
    facebook = db.Column(db.String(255))
    instagram = db.Column(db.String(255))
    tiktok = db.Column(db.String(255))
    twitter = db.Column(db.String(255))
    google = db.Column(db.String(255))
    gmail = db.Column(db.String(255))
    youtube = db.Column(db.String(255))
    pinterest = db.Column(db.String(255))
    is_active = db.Column(db.Boolean, default=True)
    # 关联组和权限
    roles = db.relationship("Role", secondary="user_roles", back_populates="users")
    permissions = db.relationship(
        "Permission", secondary="user_permissions", back_populates="users"
    )

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

    def has_permission(self, codename):
        # 检查直接分配的权限
        if any(p.codename == codename for p in self.permissions):
            return True

        # 检查角色继承的权限
        for role in self.roles:
            if any(p.codename == codename for p in role.permissions):
                return True

        return False

    def has_role(self, role_name):
        return any(role.name == role_name for role in self.roles)


class Company(db.Model):
    __tablename__ = "companies"

    id = db.Column(db.Integer, primary_key=True)
    name_cn = db.Column(db.String(100), nullable=False)
    name_en = db.Column(db.String(100), nullable=False)
    address_cn = db.Column(db.String(255), nullable=False)
    address_en = db.Column(db.String(255), nullable=False)
    website = db.Column(db.String(255), nullable=False)


# 需要添加 关于，产品，支持，FAQ，隐私政策，服务条款 / 用户协议，退款 / 退货政策

#
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('CNY', '¥', '人民币', 'Chinese Yuan', '人民币', TRUE);

# -- 美元 (全球主导货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('USD', '$', '美元', 'US Dollar', 'dollar', TRUE);

# -- 欧元 (欧盟官方货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('EUR', '€', '欧元', 'Euro', 'euro', TRUE);

# -- 日元 (亚洲主要货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('JPY', '¥', '日元', 'Japanese Yen', '円', TRUE);

# -- 英镑 (英国货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('GBP', '£', '英镑', 'British Pound', 'pound', TRUE);

# -- 澳元 (澳大利亚货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('AUD', '$', '澳元', 'Australian Dollar', 'dollar', TRUE);

# -- 加元 (加拿大货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('CAD', '$', '加元', 'Canadian Dollar', 'dollar', TRUE);

# -- 瑞士法郎 (瑞士货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('CHF', 'Fr', '瑞士法郎', 'Swiss Franc', 'Franken', TRUE);

# -- 港元 (香港货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('HKD', '$', '港元', 'Hong Kong Dollar', '港幣', TRUE);

# -- 新加坡元 (新加坡货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('SGD', '$', '新加坡元', 'Singapore Dollar', 'dollar', TRUE);

# -- 俄罗斯卢布 (俄罗斯货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('RUB', '₽', '俄罗斯卢布', 'Russian Ruble', 'рубль', TRUE);

# -- 阿联酋迪拉姆 (阿联酋货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('AED', 'د.إ', '阿联酋迪拉姆', 'UAE Dirham', 'درهم', TRUE);

# -- 沙特里亚尔 (沙特阿拉伯货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('SAR', 'ر.س', '沙特里亚尔', 'Saudi Riyal', 'ريال', TRUE);

# -- 土耳其里拉 (土耳其货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('TRY', '₺', '土耳其里拉', 'Turkish Lira', 'Türk lirası', TRUE);

# -- 韩元 (韩国货币)
# INSERT INTO currencies (code, symbol, name_cn, name_en, name_local, is_active)
# VALUES ('KRW', '₩', '韩元', 'South Korean Won', '원', TRUE);


class Currency(db.Model):
    __tablename__ = "currencies"

    id = db.Column(db.Integer, primary_key=True)
    code = db.Column(
        db.String(3), unique=True, nullable=False
    )  # ISO货币代码，如USD, CNY
    symbol = db.Column(db.String(5), nullable=False)  # 货币符号，如"$", "€", "¥"
    name_cn = db.Column(db.String(50), nullable=False)  # 中文名称，如"美元"
    name_en = db.Column(db.String(50), nullable=False)  # 英文名称，如"US Dollar"
    name_local = db.Column(db.String(50))  # 当地语言名称，如"рубль"
    is_active = db.Column(db.Boolean, default=True)  # 是否启用

    # 汇率关系(一对多)
    exchange_rates = db.relationship("ExchangeRate", backref="currency", lazy=True)

    __table_args__ = (db.Index("idx_currency_code", "code"),)


class ExchangeRate(db.Model):
    __tablename__ = "exchange_rates"

    id = db.Column(db.Integer, primary_key=True)
    currency_id = db.Column(
        db.Integer, db.ForeignKey("currencies.id"), nullable=False
    )  # 货币ID
    base_currency_code = db.Column(
        db.String(3), default="CNY", nullable=False
    )  # 基准货币代码 (默认CNY)
    rate = db.Column(
        db.Numeric(20, 6), nullable=False
    )  # 汇率 (100单位基准货币兑换目标货币的值)
    change_amount = db.Column(
        db.Numeric(20, 6)
    )  # 较前日变化金额 (正数表示涨，负数表示跌)
    # 较前日变化金额 = 今日汇率 - 昨日汇率

    change_percent = db.Column(db.Numeric(10, 4))
    # 较前日变化百分比，去掉百分号，也就是相当于乘以100  (正数表示涨，负数表示跌)
    # 较前日变化百分比 = (较前日变化金额 / 昨日汇率) × 100

    source = db.Column(db.String(100))  # 数据来源(如"中国银行")
    data_type = db.Column(db.String(20))  # 数据类型(如"现汇买入价")
    collected_date = db.Column(db.Date, nullable=False, default=date.today)  # 采集日期
    created_at = db.Column(db.DateTime, default=db.func.now())

    __table_args__ = (
        db.UniqueConstraint("currency_id", "collected_date", name="uq_currency_date"),
        db.Index("idx_exchange_rate_date", "collected_date"),
    )


class Product(db.Model):
    __tablename__ = "products"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    url = db.Column(db.String(255))
    title = db.Column(db.String(255))
    company_name = db.Column(db.String(255))
    company_url = db.Column(db.String(255))
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
    created_at = db.Column(db.DateTime, default=db.func.now())

    # 产品详情（一对多关系）
    details = db.relationship(
        "ProductDetail", backref="product", lazy=True, cascade="all, delete-orphan"
    )
    user = db.relationship("User", backref="products")


class ProductDetail(db.Model):
    __tablename__ = "product_details"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    product_id = db.Column(db.Integer, db.ForeignKey("products.id"), nullable=False)
    description = db.Column(db.String(255))  # 产品描述
    description_en = db.Column(db.String(255))  # 产品描述英文
    other_model = db.Column(db.String(255))  # 其他型号
    self_model = db.Column(db.String(255))  # 自身型号
    price = db.Column(db.Float)
    currency_id = db.Column(db.Integer, db.ForeignKey("currencies.id"))  # 新增货币关联
    image_url = db.Column(db.String(255))
    stock = db.Column(db.Numeric(10, 3))
    local_image_path = db.Column(db.String(255), nullable=True)

    currency = db.relationship("Currency")
    # 图片记录（一对多关系）
    image_records = db.relationship(
        "ImageRecord", backref="detail", lazy=True, cascade="all, delete-orphan"
    )

    # 批发价格（一对多关系）
    wholesale_prices = db.relationship(
        "WholesalePrice", backref="detail", lazy=True, cascade="all, delete-orphan"
    )


class WholesalePrice(db.Model):
    __tablename__ = "wholesale_prices"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    detail_id = db.Column(
        db.Integer, db.ForeignKey("product_details.id"), nullable=False
    )
    min_quantity = db.Column(db.Integer, nullable=False)  # 最小起订量
    max_quantity = db.Column(db.Integer)  # 最大起订量（None表示无上限）
    price = db.Column(
        db.String(20), nullable=False
    )  # 价格（支持字符串格式，如带货币符号）
    currency_id = db.Column(db.Integer, db.ForeignKey("currencies.id"))  # 新增货币关联

    currency = db.relationship("Currency")

    __table_args__ = (
        db.UniqueConstraint("detail_id", "min_quantity", name="uq_detail_min_quantity"),
    )


class ImageRecord(db.Model):
    __tablename__ = "image_records"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    detail_id = db.Column(
        db.Integer, db.ForeignKey("product_details.id"), nullable=False
    )
    image_url = db.Column(db.String(255), nullable=False)
    local_path = db.Column(db.String(255), nullable=False)
    download_time = db.Column(db.DateTime, default=db.func.current_timestamp())


# 报价单购物车
class QuotationCart(db.Model):
    __tablename__ = "quotation_carts"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    created_at = db.Column(db.DateTime, default=db.func.now())
    updated_at = db.Column(db.DateTime, default=db.func.now(), onupdate=db.func.now())

    # 关联用户
    user = db.relationship("User", backref="quotation_carts")
    # 购物车中的产品项
    items = db.relationship(
        "QuotationCartItem", backref="cart", lazy=True, cascade="all, delete-orphan"
    )


# 报价单购物车项
class QuotationCartItem(db.Model):
    __tablename__ = "quotation_cart_items"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    cart_id = db.Column(db.Integer, db.ForeignKey("quotation_carts.id"), nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey("products.id"), nullable=False)
    product_detail_id = db.Column(
        db.Integer, db.ForeignKey("product_details.id"), nullable=True
    )

    # 关联产品和产品详情
    product = db.relationship("Product", backref="cart_items")
    product_detail = db.relationship("ProductDetail", backref="cart_items")

    __table_args__ = (
        db.UniqueConstraint(
            "cart_id", "product_id", "product_detail_id", name="uq_cart_product_detail"
        ),
    )


# 报价单
class Quotation(db.Model):
    __tablename__ = "quotations"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    company_id = db.Column(db.Integer, db.ForeignKey("companies.id"), nullable=True)
    quotation_number = db.Column(
        db.String(50), unique=True, nullable=False
    )  # 报价单编号
    created_at = db.Column(db.DateTime, default=db.func.now())
    updated_at = db.Column(db.DateTime, default=db.func.now(), onupdate=db.func.now())
    status = db.Column(
        db.String(20), default="draft"
    )  # 状态：draft, confirmed, cancelled

    # 关联用户
    user = db.relationship("User", backref="quotations")

    # 关联公司
    company = db.relationship("Company", backref="quotations")

    # 报价单中的产品项
    items = db.relationship(
        "QuotationItem", backref="quotation", lazy=True, cascade="all, delete-orphan"
    )


# 报价单项
class QuotationItem(db.Model):
    __tablename__ = "quotation_items"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    quotation_id = db.Column(db.Integer, db.ForeignKey("quotations.id"), nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey("products.id"), nullable=False)
    product_detail_id = db.Column(
        db.Integer, db.ForeignKey("product_details.id"), nullable=True
    )

    # 成本价相关字段
    cost_price = db.Column(db.Numeric(20, 6))  # 成本单价(人民币)
    cost_currency_id = db.Column(
        db.Integer, db.ForeignKey("currencies.id"), default=1
    )  # 成本货币ID(默认CNY，由于使用了初始化设置，CNY是第一条插入的数据，因此id必定为1)

    # 报价相关字段
    unit_price = db.Column(db.Numeric(20, 6))  # 报价单价
    quote_currency_id = db.Column(
        db.Integer, db.ForeignKey("currencies.id"), default=1
    )  # 报价货币ID 默认 CNY

    # 利润计算字段
    profit_amount = db.Column(db.Numeric(20, 6))  # 单件利润金额(quote_currency)
    profit_percentage = db.Column(db.Numeric(10, 4))  # 利润率百分比

    # 关联关系
    product = db.relationship("Product", backref="quotation_items")
    product_detail = db.relationship("ProductDetail", backref="quotation_items")
    cost_currency = db.relationship("Currency", foreign_keys=[cost_currency_id])
    quote_currency = db.relationship("Currency", foreign_keys=[quote_currency_id])
